﻿namespace Chain.IDAL
{
    using Chain.DBUtility;
    using Chain.Model;
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class GoodsLogDetail
    {
        public int Add(Chain.Model.GoodsLogDetail model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into GoodsLogDetail(");
            builder.Append("GoodsLogID,GoodsID,GoodsInPrice,GoodsOutPrice,GoodsNumber)");
            builder.Append(" values (");
            builder.Append("@GoodsLogID,@GoodsID,@GoodsInPrice,@GoodsOutPrice,@GoodsNumber)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@GoodsLogID", SqlDbType.Int, 4), new SqlParameter("@GoodsID", SqlDbType.Int, 4), new SqlParameter("@GoodsInPrice", SqlDbType.Money, 8), new SqlParameter("@GoodsOutPrice", SqlDbType.Money, 8), new SqlParameter("@GoodsNumber", SqlDbType.Float, 8) };
            cmdParms[0].Value = model.GoodsLogID;
            cmdParms[1].Value = model.GoodsID;
            cmdParms[2].Value = model.GoodsInPrice;
            cmdParms[3].Value = model.GoodsOutPrice;
            cmdParms[4].Value = model.GoodsNumber;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool Delete(int GoodsLogDetailID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from GoodsLogDetail ");
            builder.Append(" where GoodsLogDetailID=@GoodsLogDetailID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@GoodsLogDetailID", SqlDbType.Int, 4) };
            cmdParms[0].Value = GoodsLogDetailID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool DeleteDetail(int goodsLogID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from GoodsLogDetail ");
            builder.Append(" where GoodsLogID=@GoodsLogID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@GoodsLogID", SqlDbType.Int, 4) };
            cmdParms[0].Value = goodsLogID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool DeleteList(string GoodsLogDetailIDlist)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from GoodsLogDetail ");
            builder.Append(" where GoodsLogDetailID in (" + GoodsLogDetailIDlist + ")  ");
            return (DbHelperSQL.ExecuteSql(builder.ToString()) > 0);
        }

        public bool ExeclDataInput(ArrayList sqlArray)
        {
            return DbHelperSQL.ExecuteSqlTran(sqlArray);
        }

        public bool Exists(int GoodsLogDetailID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from GoodsLogDetail");
            builder.Append(" where GoodsLogDetailID=@GoodsLogDetailID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@GoodsLogDetailID", SqlDbType.Int, 4) };
            cmdParms[0].Value = GoodsLogDetailID;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public DataSet getGoodsLogDetail(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" select GoodsLogDetail.*,Goods.Name,Goods.GoodsCode ");
            builder.Append(" from GoodsLogDetail,Goods");
            builder.Append(" where GoodsLogDetail.GoodsID=Goods.GoodsID and " + strWhere);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetInsufficientCount(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ISNULL(COUNT(1),0) AS InsufficientCount FROM GoodsLogDetail INNER JOIN GoodsLog ON GoodsLogDetail.GoodsLogID = GoodsLog.ID  INNER JOIN GoodsNumber ON GoodsNumber.GoodsID = GoodsLogDetail.GoodsID  AND GoodsNumber.ShopID = GoodsLog.ChangeShopID WHERE GoodsLogDetail.GoodsNumber > GoodsNumber.Number");
            builder.Append(" AND " + strWhere);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetList(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select GoodsLogDetailID,GoodsLogID,GoodsID,GoodsInPrice,GoodsOutPrice,GoodsNumber ");
            builder.Append(" FROM GoodsLogDetail ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (Top > 0)
            {
                builder.Append(" top " + Top.ToString());
            }
            builder.Append(" GoodsLogDetailID,GoodsLogID,GoodsID,GoodsInPrice,GoodsOutPrice,GoodsNumber ");
            builder.Append(" FROM GoodsLogDetail ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT * FROM ( ");
            builder.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                builder.Append("order by T." + orderby);
            }
            else
            {
                builder.Append("order by T.GoodsLogDetailID desc");
            }
            builder.Append(")AS Row, T.*  from GoodsLogDetail T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                builder.Append(" WHERE " + strWhere);
            }
            builder.Append(" ) TT");
            builder.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetListSP(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" select GoodsLogDetail.*,Goods.Name,Goods.GoodsCode,Goods.GoodsType");
            builder.Append(" from GoodsLogDetail,Goods");
            builder.Append(" where " + strWhere);
            return DbHelperSQL.Query(builder.ToString());
        }

        public Chain.Model.GoodsLogDetail GetModel(int GoodsLogDetailID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select  top 1 GoodsLogDetailID,GoodsLogID,GoodsID,GoodsInPrice,GoodsOutPrice,GoodsNumber from GoodsLogDetail ");
            builder.Append(" where GoodsLogDetailID=@GoodsLogDetailID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@GoodsLogDetailID", SqlDbType.Int, 4) };
            cmdParms[0].Value = GoodsLogDetailID;
            Chain.Model.GoodsLogDetail detail = new Chain.Model.GoodsLogDetail();
            DataSet set = DbHelperSQL.Query(builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count <= 0)
            {
                return null;
            }
            if ((set.Tables[0].Rows[0]["GoodsLogDetailID"] != null) && (set.Tables[0].Rows[0]["GoodsLogDetailID"].ToString() != ""))
            {
                detail.GoodsLogDetailID = int.Parse(set.Tables[0].Rows[0]["GoodsLogDetailID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["GoodsLogID"] != null) && (set.Tables[0].Rows[0]["GoodsLogID"].ToString() != ""))
            {
                detail.GoodsLogID = int.Parse(set.Tables[0].Rows[0]["GoodsLogID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["GoodsID"] != null) && (set.Tables[0].Rows[0]["GoodsID"].ToString() != ""))
            {
                detail.GoodsID = int.Parse(set.Tables[0].Rows[0]["GoodsID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["GoodsInPrice"] != null) && (set.Tables[0].Rows[0]["GoodsInPrice"].ToString() != ""))
            {
                detail.GoodsInPrice = decimal.Parse(set.Tables[0].Rows[0]["GoodsInPrice"].ToString());
            }
            if ((set.Tables[0].Rows[0]["GoodsOutPrice"] != null) && (set.Tables[0].Rows[0]["GoodsOutPrice"].ToString() != ""))
            {
                detail.GoodsOutPrice = decimal.Parse(set.Tables[0].Rows[0]["GoodsOutPrice"].ToString());
            }
            if ((set.Tables[0].Rows[0]["GoodsNumber"] != null) && (set.Tables[0].Rows[0]["GoodsNumber"].ToString() != ""))
            {
                detail.GoodsNumber = decimal.Parse(set.Tables[0].Rows[0]["GoodsNumber"].ToString());
            }
            return detail;
        }

        public int GetRecordCount(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) FROM GoodsLogDetail ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            object single = DbHelperSQL.GetSingle(builder.ToString());
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool Update(Chain.Model.GoodsLogDetail model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update GoodsLogDetail set ");
            builder.Append("GoodsLogID=@GoodsLogID,");
            builder.Append("GoodsID=@GoodsID,");
            builder.Append("GoodsInPrice=@GoodsInPrice,");
            builder.Append("GoodsOutPrice=@GoodsOutPrice,");
            builder.Append("GoodsNumber=@GoodsNumber");
            builder.Append(" where GoodsLogDetailID=@GoodsLogDetailID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@GoodsLogID", SqlDbType.Int, 4), new SqlParameter("@GoodsID", SqlDbType.Int, 4), new SqlParameter("@GoodsInPrice", SqlDbType.Money, 8), new SqlParameter("@GoodsOutPrice", SqlDbType.Money, 8), new SqlParameter("@GoodsNumber", SqlDbType.Float, 8), new SqlParameter("@GoodsLogDetailID", SqlDbType.Int, 4) };
            cmdParms[0].Value = model.GoodsLogID;
            cmdParms[1].Value = model.GoodsID;
            cmdParms[2].Value = model.GoodsInPrice;
            cmdParms[3].Value = model.GoodsOutPrice;
            cmdParms[4].Value = model.GoodsNumber;
            cmdParms[5].Value = model.GoodsLogDetailID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }
    }
}

